﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Windows;

namespace WpfApp3.Data
{
    public class SQLiteManager
    {
        private string connectionString = "Data Source=Sql/data.db;";

        // 修正构造函数名称
        /// <summary>
        /// 初始化SQl
        /// </summary>
        /// /// <param name="SQLiteManager"></param>
        public SQLiteManager()
        {
            
            try
            {
                // 从连接字符串中提取目录部分//Sql/data.db
                string dbPath = connectionString.Split('=')[1].Trim(';', ' ');
                string dbDirectory = Path.GetDirectoryName(Path.GetFullPath(dbPath));
                if (!Directory.Exists(dbDirectory))
                {
                    Directory.CreateDirectory(dbDirectory);
                }

                // 执行数据库连接和表创建
                CreateTableIfNotExists();
                MessageBox.Show("sql数据库初始化成功");
            }
            catch (Exception ex)
            {
                MessageBox.Show($"数据库初始化失败: {ex.Message}");
                // 实际项目中建议记录日志
                Debug.WriteLine($"SQLite初始化错误: {ex}");
            }
        }
        /// <summary>
        /// 创建表，初始化表
        /// </summary>
        /// <param name="CreateTableIfNotExists("></param>
        private void CreateTableIfNotExists()
        {            //SQLiteConnection实现了IDisposable接口调用Dispose()释放资源(如关闭文件、网络连接或数据库连接）
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))//using 语句用于确保实现了 IDisposable 接口的对象被正确释放资源
                                                                            //这是一种语法糖，等价于在 try 块中使用对象，并在 finally 块中调用 Dispose() 方法。
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    // 创建表（如果不存在） - 恢复完整的表结构
                    command.CommandText = @"
                CREATE TABLE IF NOT EXISTS Persons (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT,
                    Age TEXT,
                    Gender TEXT,
                    Characteristics TEXT,
                    Marital TEXT,
                    Political TEXT,
                    Remarks TEXT
                )";

                    command.ExecuteNonQuery();
                }
            }
        }
        
        /// <summary>
        /// 添加人员到数据库
        /// </summary>
        /// <param name="AddPerson"></param>
        public void AddPerson(WpfApp3.Models.Person person)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "INSERT INTO Persons (Name, Age, Gender, Characteristics, Marital, Political, Remarks) VALUES (@Name, @Age, @Gender, @Characteristics, @Marital, @Political, @Remarks)";
                    command.Parameters.AddWithValue("@Name", person.Name);
                    command.Parameters.AddWithValue("@Age", person.Age);
                    command.Parameters.AddWithValue("@Gender", person.Gender);
                    command.Parameters.AddWithValue("@Characteristics", person.Characteristics);
                    command.Parameters.AddWithValue("@Marital", person.Marital);
                    command.Parameters.AddWithValue("@Political", person.Political);
                    command.Parameters.AddWithValue("@Remarks", person.Remarks);
                    command.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 从数据库中删除人员
        /// </summary>
        /// <param name="DeletePerson"></param>
        public void DeletePerson(string name)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "DELETE FROM Persons WHERE Name = @Name";
                    command.Parameters.AddWithValue("@Name", name);
                    command.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 修改更新数据库中的人员信息
        /// </summary>
        /// <param name="UpdatePerson"></param>
        public void UpdatePerson(WpfApp3.Models.Person person)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = "UPDATE Persons SET Age = @Age, Gender = @Gender, Characteristics = @Characteristics, Marital = @Marital, Political = @Political, Remarks = @Remarks WHERE Name = @Name";
                    command.Parameters.AddWithValue("@Name", person.Name);
                    command.Parameters.AddWithValue("@Age", person.Age);
                    command.Parameters.AddWithValue("@Gender", person.Gender);
                    command.Parameters.AddWithValue("@Characteristics", person.Characteristics);
                    command.Parameters.AddWithValue("@Marital", person.Marital);
                    command.Parameters.AddWithValue("@Political", person.Political);
                    command.Parameters.AddWithValue("@Remarks", person.Remarks);
                    command.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// 从数据库中获取所有人员信息
        /// </summary>
        /// <param name="GetAllPersons"></param>
        public List<WpfApp3.Models.Person> GetAllPersons()
        {
            List<WpfApp3.Models.Person> persons = new List<WpfApp3.Models.Person>();
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM Persons", connection))
                {
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            WpfApp3.Models.Person person = new WpfApp3.Models.Person
                            {
                                Name = reader["Name"].ToString(),
                                Age = Convert.ToInt32(reader["Age"]),
                                Gender = reader["Gender"].ToString(),
                                Characteristics = reader["Characteristics"].ToString(),
                                Marital = reader["Marital"].ToString(),
                                Political = reader["Political"].ToString(),
                                Remarks = reader["Remarks"].ToString()
                            };
                            persons.Add(person);
                        }
                    }
                }
            }
            return persons;
        }
    }
}